package org.third.integration.hibernate;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonPropertyOrder;
import com.fasterxml.jackson.annotation.JsonView;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Transient;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

class MetadataKeyValue implements Serializable {
    /** Column name for metadata key */
    public static final String MetadataKeyColumnName = "FIELD_KEY";
    /** Column name for metadata value */
    public static final String MetadataValueColumnName = "FIELD_VALUE";
    /** Id column identifier */
    public static final String UNIQUE_ID_COLUMN_NAME = "UUID";

    private String key;
    private String value;
    private AbstractUserData user;

    public MetadataKeyValue() {
    }

    public MetadataKeyValue(String key, String value) {
        this.key = key;
        this.value = value;
    }

    public MetadataKeyValue(String key, String value, AbstractUserData user) {
        this.key = key;
        this.value = value;
        this.user = user;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = UNIQUE_ID_COLUMN_NAME)
    public AbstractUserData getUser() {
        return user;
    }

    public void setUser(AbstractUserData abstractUser) {
        this.user = abstractUser;
    }

    @Column(name = MetadataKeyColumnName, length = 255)
    @JsonView
    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    @Column(name = MetadataValueColumnName, length = 255)
    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof MetadataKeyValue)) {
            return false;
        }

        MetadataKeyValue that = (MetadataKeyValue) o;

        if (key != null ? !key.equals(that.key) : that.key != null) {
            return false;
        }
        if (value != null ? !value.equals(that.value) : that.value != null) {
            return false;
        }
        return !(user != null ? !user.equals(that.user) : that.user != null);

    }

    @Override
    public int hashCode() {
        int result = key != null ? key.hashCode() : 0;
        result = 31 * result + (value != null ? value.hashCode() : 0);
        result = 31 * result + (user != null ? user.hashCode() : 0);
        return result;
    }
}

@Entity
@JsonIgnoreProperties(ignoreUnknown = true)
@JsonSerialize(include = JsonSerialize.Inclusion.NON_NULL)
@Table(name = AbstractUserMetadata.UserMetadataTableName)
class AbstractUserMetadata implements Serializable {

    /** Table name for metadata collection */
    public static final String UserMetadataTableName = "ABSTRACT_USER_METADATA";

    private MetadataKeyValue metadataKeyValue;

    public AbstractUserMetadata() {
    }

    public AbstractUserMetadata(String key, String value) {
        metadataKeyValue = new MetadataKeyValue(key, value);
    }

    public AbstractUserMetadata(AbstractUserData user, String key, String value) {
        metadataKeyValue = new MetadataKeyValue(key, value, user);
    }

    @EmbeddedId
    public MetadataKeyValue getMetadataKeyValue() {
        return metadataKeyValue;
    }

    public void setMetadataKeyValue(MetadataKeyValue metadataKeyValue) {
        this.metadataKeyValue = metadataKeyValue;
    }

    @Transient
    public String getKey() {
        return metadataKeyValue.getKey();
    }

    public void setKey(String key) {
        metadataKeyValue.setKey(key);
    }

    @Transient
    public String getValue() {
        return metadataKeyValue.getValue();
    }

    public void setValue(String value) {
        metadataKeyValue.setValue(value);
    }

    @Transient
    public AbstractUserData getUser() {
        return metadataKeyValue.getUser();
    }

    public void setUser(AbstractUserData user) {
        metadataKeyValue.setUser(user);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (!(o instanceof AbstractUserMetadata)) {
            return false;
        }

        AbstractUserMetadata that = (AbstractUserMetadata) o;

        return !(metadataKeyValue != null ? !metadataKeyValue.equals(that.metadataKeyValue) : that.metadataKeyValue != null);

    }

    @Override
    public int hashCode() {
        return metadataKeyValue != null ? metadataKeyValue.hashCode() : 0;
    }
}

@JsonIgnoreProperties(ignoreUnknown = true)
@JsonSerialize(include = JsonSerialize.Inclusion.NON_NULL)
@JsonPropertyOrder(value = { "metadata" })
class AbstractUserData {
    private Set<AbstractUserMetadata> metadata = new LinkedHashSet<>();

    /**
     * Legacy API + JSON serialization Resolves the duplicated keys in abstractUserMetadata by adding a suffix ex:
     * key1-> value1 key1(1)->value2 key1(2)->value3
     * 
     * @return see {@link #getMetadata()}
     */
    @JsonProperty("metadata")
    @Transient
    public Map<String, String> getMetadataMap() {
        // TODO multi-key resolution
        Map<String, Integer> duplicateIndexer = new HashMap<>();
        Map<String, String> metadataMap = new HashMap<>();
        for (AbstractUserMetadata abstractUserMetadata : metadata) {
            if (duplicateIndexer.containsKey(abstractUserMetadata.getKey())) {
                Integer duplicateCount = duplicateIndexer.get(abstractUserMetadata.getKey()) + 1;
                String indexSuffix = "(" + duplicateCount + ")";
                duplicateIndexer.put(abstractUserMetadata.getKey(), duplicateCount);
                metadataMap.put(abstractUserMetadata.getKey() + indexSuffix, abstractUserMetadata.getValue());
            } else {
                duplicateIndexer.put(abstractUserMetadata.getKey(), 1);
                metadataMap.put(abstractUserMetadata.getKey(), abstractUserMetadata.getValue());
            }
        }
        return metadataMap;
    }

    /**
     * Legacy API + JSON serialization
     * 
     * @return see {@link #setMetadata(java.util.Set)} ()}
     */
    @JsonProperty("metadata")
    public void setMetadataMap(Map<String, String> json) {
        metadata = json.entrySet().stream().map(e -> new AbstractUserMetadata(this, e.getKey(), e.getValue())).collect(Collectors.toSet());
    }

}

class TestToken<T> implements Comparable<T> {
    Integer t;

    TestToken(Integer t) {
        this.t = t;
    }

    @Override
    public int compareTo(T o) {
        if (o instanceof TestToken) {
            return t.compareTo(((TestToken<?>) o).t);
        }
        return t.compareTo(o.hashCode());
    }

    @Override
    public String toString() {
        return t + "";
    }

}

public abstract class DbTest {
    public void printConnection(Connection conn) throws SQLException {
        println("======connection info");
        println("catalog=" + conn.getCatalog() + ",schema=" + conn.getSchema() + ",warning=" + conn.getWarnings() + ",typeMap="
                + conn.getTypeMap());

        DatabaseMetaData md = conn.getMetaData();
        println("=====Print Database metadatas");
        printDatabaseMetadata(md);
    }

    public void printDatabaseMetadata(DatabaseMetaData md) throws SQLException {
        println("allProceduresAreCallable                                =" + md.allProceduresAreCallable());
        println("allTablesAreSelectable                                  =" + md.allTablesAreSelectable());
        println("autoCommitFailureClosesAllResultSets                    =" + md.autoCommitFailureClosesAllResultSets());
        println("dataDefinitionCausesTransactionCommit                   =" + md.dataDefinitionCausesTransactionCommit());
        println("dataDefinitionIgnoredInTransactions                     =" + md.dataDefinitionIgnoredInTransactions());
        println("doesMaxRowSizeIncludeBlobs                              =" + md.doesMaxRowSizeIncludeBlobs());
        println("generatedKeyAlwaysReturned                              =" + md.generatedKeyAlwaysReturned());
        println("getCatalogs                                             =" + md.getCatalogs());
        println("getCatalogSeparator                                     =" + md.getCatalogSeparator());
        println("getCatalogTerm                                          =" + md.getCatalogTerm());
        println("getClientInfoProperties                                 =" + md.getClientInfoProperties());
        println("getConnection                                           =" + md.getConnection());
        println("getDatabaseMajorVersion                                 =" + md.getDatabaseMajorVersion());
        println("getDatabaseMinorVersion                                 =" + md.getDatabaseMinorVersion());
        println("getDatabaseProductName                                  =" + md.getDatabaseProductName());
        println("getDatabaseProductVersion                               =" + md.getDatabaseProductVersion());
        println("getDefaultTransactionIsolation                          =" + md.getDefaultTransactionIsolation());
        println("getDriverMajorVersion                                   =" + md.getDriverMajorVersion());
        println("getDriverMinorVersion                                   =" + md.getDriverMinorVersion());
        println("getDriverName                                           =" + md.getDriverName());
        println("getDriverVersion                                        =" + md.getDriverVersion());
        println("getExtraNameCharacters                                  =" + md.getExtraNameCharacters());
        println("getIdentifierQuoteString                                =" + md.getIdentifierQuoteString());
        println("getJDBCMajorVersion                                     =" + md.getJDBCMajorVersion());
        println("getJDBCMinorVersion                                     =" + md.getJDBCMinorVersion());
        println("getMaxBinaryLiteralLength                               =" + md.getMaxBinaryLiteralLength());
        println("getMaxCatalogNameLength                                 =" + md.getMaxCatalogNameLength());
        println("getMaxCharLiteralLength                                 =" + md.getMaxCharLiteralLength());
        println("getMaxColumnNameLength                                  =" + md.getMaxColumnNameLength());
        println("getMaxColumnsInGroupBy                                  =" + md.getMaxColumnsInGroupBy());
        println("getMaxColumnsInIndex                                    =" + md.getMaxColumnsInIndex());
        println("getMaxColumnsInOrderBy                                  =" + md.getMaxColumnsInOrderBy());
        println("getMaxColumnsInSelect                                   =" + md.getMaxColumnsInSelect());
        println("getMaxColumnsInTable                                    =" + md.getMaxColumnsInTable());
        println("getMaxConnections                                       =" + md.getMaxConnections());
        println("getMaxCursorNameLength                                  =" + md.getMaxCursorNameLength());
        println("getMaxIndexLength                                       =" + md.getMaxIndexLength());
        println("getMaxLogicalLobSize                                    =" + md.getMaxLogicalLobSize());
        println("getMaxProcedureNameLength                               =" + md.getMaxProcedureNameLength());
        println("getMaxRowSize                                           =" + md.getMaxRowSize());
        println("getMaxSchemaNameLength                                  =" + md.getMaxSchemaNameLength());
        println("getMaxStatementLength                                   =" + md.getMaxStatementLength());
        println("getMaxStatements                                        =" + md.getMaxStatements());
        println("getMaxTableNameLength                                   =" + md.getMaxTableNameLength());
        println("getMaxTablesInSelect                                    =" + md.getMaxTablesInSelect());
        println("getMaxUserNameLength                                    =" + md.getMaxUserNameLength());
        println("getNumericFunctions                                     =" + md.getNumericFunctions());
        println("getProcedureTerm                                        =" + md.getProcedureTerm());
        println("getResultSetHoldability                                 =" + md.getResultSetHoldability());
        println("getRowIdLifetime                                        =" + md.getRowIdLifetime());
        println("getSchemas                                              =" + md.getSchemas());
        println("getSchemaTerm                                           =" + md.getSchemaTerm());
        println("getSearchStringEscape                                   =" + md.getSearchStringEscape());
        println("getSQLKeywords                                          =" + md.getSQLKeywords());
        println("getSQLStateType                                         =" + md.getSQLStateType());
        println("getStringFunctions                                      =" + md.getStringFunctions());
        println("getSystemFunctions                                      =" + md.getSystemFunctions());
        println("getTableTypes                                           =" + md.getTableTypes());
        println("getTimeDateFunctions                                    =" + md.getTimeDateFunctions());
        println("getTypeInfo                                             =" + md.getTypeInfo());
        println("getURL                                                  =" + md.getURL());
        println("getUserName                                             =" + md.getUserName());
        println("isCatalogAtStart                                        =" + md.isCatalogAtStart());
        println("isReadOnly                                              =" + md.isReadOnly());
        println("locatorsUpdateCopy                                      =" + md.locatorsUpdateCopy());
        println("nullPlusNonNullIsNull                                   =" + md.nullPlusNonNullIsNull());
        println("nullsAreSortedAtEnd                                     =" + md.nullsAreSortedAtEnd());
        println("nullsAreSortedAtStart                                   =" + md.nullsAreSortedAtStart());
        println("nullsAreSortedHigh                                      =" + md.nullsAreSortedHigh());
        println("nullsAreSortedLow                                       =" + md.nullsAreSortedLow());
        println("storesLowerCaseIdentifiers                              =" + md.storesLowerCaseIdentifiers());
        println("storesLowerCaseQuotedIdentifiers                        =" + md.storesLowerCaseQuotedIdentifiers());
        println("storesMixedCaseIdentifiers                              =" + md.storesMixedCaseIdentifiers());
        println("storesMixedCaseQuotedIdentifiers                        =" + md.storesMixedCaseQuotedIdentifiers());
        println("storesUpperCaseIdentifiers                              =" + md.storesUpperCaseIdentifiers());
        println("storesUpperCaseQuotedIdentifiers                        =" + md.storesUpperCaseQuotedIdentifiers());
        println("supportsAlterTableWithAddColumn                         =" + md.supportsAlterTableWithAddColumn());
        println("supportsAlterTableWithDropColumn                        =" + md.supportsAlterTableWithDropColumn());
        println("supportsANSI92EntryLevelSQL                             =" + md.supportsANSI92EntryLevelSQL());
        println("supportsANSI92FullSQL                                   =" + md.supportsANSI92FullSQL());
        println("supportsANSI92IntermediateSQL                           =" + md.supportsANSI92IntermediateSQL());
        println("supportsBatchUpdates                                    =" + md.supportsBatchUpdates());
        println("supportsCatalogsInDataManipulation                      =" + md.supportsCatalogsInDataManipulation());
        println("supportsCatalogsInIndexDefinitions                      =" + md.supportsCatalogsInIndexDefinitions());
        println("supportsCatalogsInPrivilegeDefinitions                  =" + md.supportsCatalogsInPrivilegeDefinitions());
        println("supportsCatalogsInProcedureCalls                        =" + md.supportsCatalogsInProcedureCalls());
        println("supportsCatalogsInTableDefinitions                      =" + md.supportsCatalogsInTableDefinitions());
        println("supportsColumnAliasing                                  =" + md.supportsColumnAliasing());
        println("supportsConvert                                         =" + md.supportsConvert());
        println("supportsCoreSQLGrammar                                  =" + md.supportsCoreSQLGrammar());
        println("supportsCorrelatedSubqueries                            =" + md.supportsCorrelatedSubqueries());
        println("supportsDataDefinitionAndDataManipulationTransactions   =" + md.supportsDataDefinitionAndDataManipulationTransactions());
        println("supportsDataManipulationTransactionsOnly                =" + md.supportsDataManipulationTransactionsOnly());
        println("supportsDifferentTableCorrelationNames                  =" + md.supportsDifferentTableCorrelationNames());
        println("supportsExpressionsInOrderBy                            =" + md.supportsExpressionsInOrderBy());
        println("supportsExtendedSQLGrammar                              =" + md.supportsExtendedSQLGrammar());
        println("supportsFullOuterJoins                                  =" + md.supportsFullOuterJoins());
        println("supportsGetGeneratedKeys                                =" + md.supportsGetGeneratedKeys());
        println("supportsGroupBy                                         =" + md.supportsGroupBy());
        println("supportsGroupByBeyondSelect                             =" + md.supportsGroupByBeyondSelect());
        println("supportsGroupByUnrelated                                =" + md.supportsGroupByUnrelated());
        println("supportsIntegrityEnhancementFacility                    =" + md.supportsIntegrityEnhancementFacility());
        println("supportsLikeEscapeClause                                =" + md.supportsLikeEscapeClause());
        println("supportsLimitedOuterJoins                               =" + md.supportsLimitedOuterJoins());
        println("supportsMinimumSQLGrammar                               =" + md.supportsMinimumSQLGrammar());
        println("supportsMixedCaseIdentifiers                            =" + md.supportsMixedCaseIdentifiers());
        println("supportsMixedCaseQuotedIdentifiers                      =" + md.supportsMixedCaseQuotedIdentifiers());
        println("supportsMultipleOpenResults                             =" + md.supportsMultipleOpenResults());
        println("supportsMultipleResultSets                              =" + md.supportsMultipleResultSets());
        println("supportsMultipleTransactions                            =" + md.supportsMultipleTransactions());
        println("supportsNamedParameters                                 =" + md.supportsNamedParameters());
        println("supportsNonNullableColumns                              =" + md.supportsNonNullableColumns());
        println("supportsOpenCursorsAcrossCommit                         =" + md.supportsOpenCursorsAcrossCommit());
        println("supportsOpenCursorsAcrossRollback                       =" + md.supportsOpenCursorsAcrossRollback());
        println("supportsOpenStatementsAcrossCommit                      =" + md.supportsOpenStatementsAcrossCommit());
        println("supportsOpenStatementsAcrossRollback                    =" + md.supportsOpenStatementsAcrossRollback());
        println("supportsOrderByUnrelated                                =" + md.supportsOrderByUnrelated());
        println("supportsOuterJoins                                      =" + md.supportsOuterJoins());
        println("supportsPositionedDelete                                =" + md.supportsPositionedDelete());
        println("supportsPositionedUpdate                                =" + md.supportsPositionedUpdate());
        println("supportsRefCursors                                      =" + md.supportsRefCursors());
        println("supportsSavepoints                                      =" + md.supportsSavepoints());
        println("supportsSchemasInDataManipulation                       =" + md.supportsSchemasInDataManipulation());
        println("supportsSchemasInIndexDefinitions                       =" + md.supportsSchemasInIndexDefinitions());
        println("supportsSchemasInPrivilegeDefinitions                   =" + md.supportsSchemasInPrivilegeDefinitions());
        println("supportsSchemasInProcedureCalls                         =" + md.supportsSchemasInProcedureCalls());
        println("supportsSchemasInTableDefinitions                       =" + md.supportsSchemasInTableDefinitions());
        println("supportsSelectForUpdate                                 =" + md.supportsSelectForUpdate());
        println("supportsStatementPooling                                =" + md.supportsStatementPooling());
        println("supportsStoredFunctionsUsingCallSyntax                  =" + md.supportsStoredFunctionsUsingCallSyntax());
        println("supportsStoredProcedures                                =" + md.supportsStoredProcedures());
        println("supportsSubqueriesInComparisons                         =" + md.supportsSubqueriesInComparisons());
        println("supportsSubqueriesInExists                              =" + md.supportsSubqueriesInExists());
        println("supportsSubqueriesInIns                                 =" + md.supportsSubqueriesInIns());
        println("supportsSubqueriesInQuantifieds                         =" + md.supportsSubqueriesInQuantifieds());
        println("supportsTableCorrelationNames                           =" + md.supportsTableCorrelationNames());
        println("supportsTransactions                                    =" + md.supportsTransactions());
        println("supportsUnion                                           =" + md.supportsUnion());
        println("supportsUnionAll                                        =" + md.supportsUnionAll());
        println("usesLocalFilePerTable                                   =" + md.usesLocalFilePerTable());
        println("usesLocalFiles                                          =" + md.usesLocalFiles());

        println("=====Print getSchemas()");
        printResultSet(md.getSchemas());
        println("=====Print getCatalogs()");
        printResultSet(md.getCatalogs());
        println("=====Print getTableTypes()");
        printResultSet(md.getTableTypes());
        println("=====Print getTables()");
        printResultSet(md.getTables(null, null, null, new String[] { "TABLE" })); // Typical types are "TABLE", "VIEW",
        // "SYSTEM TABLE",
        // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
        // "SYNONYM
        // println("=====Print getProcedures()");
        // printResultSet(md.getProcedures(null, null, null));
    }

    enum OutputFormat {
        HTML, TEXT
    }

    static OutputFormat currentFormat = OutputFormat.HTML;

    public void printResultSet(ResultSet rs, String... tableName) throws SQLException {
        ResultSetMetaData rsMetadata = rs.getMetaData();

        int line = 0;
        while (rs.next()) {
            if (currentFormat == OutputFormat.HTML) {
                if (line == 0) {
                    println(String.format("catalog=%s, schema=%s, table=%s", rsMetadata.getCatalogName(1), rsMetadata.getSchemaName(1),
                            rsMetadata.getTableName(0)));
                    println("");
                    println("<table border=1>");
                }
                print("<tr>");
                for (int c = 1, size = rsMetadata.getColumnCount(); c <= size; c++, print("")) {
                    if (c == 1 && line == 0) {
                        for (int x = 1; x <= size; x++, print("")) {
                            print("<td>" + rsMetadata.getColumnName(x) + "</td>");
                        }
                        println("</tr>");
                        print("<tr>");
                    }
                    print("<td>" + rs.getObject(c) + "</td>");
                }
                println("</tr>");
            } else {
                if (line == 0) {
                    println("==Table:" + rsMetadata.getCatalogName(1) + "." + rsMetadata.getSchemaName(1) + "."
                            + rsMetadata.getTableName(1));
                }
                for (int c = 1, size = rsMetadata.getColumnCount(); c <= size; c++, print(",")) {
                    if (c == 1 && line == 0) {
                        for (int x = 1; x <= size; x++, print(",")) {
                            if (x == 1) {
                                print("    ");
                            }
                            print(rsMetadata.getColumnName(x));
                        }
                        println("\n--------------------------------");
                    }
                    if (c == 1) {
                        print("L" + line + ": ");
                    }
                    print(rs.getObject(c));
                }
                println("");
            }
            line++;
        }
        if (currentFormat == OutputFormat.HTML) {
            println("</table>");
        }
    }

    public abstract void testDatabase();

    public static <T> void print(T t) {
        System.out.print(t);
    }

    public static <T> void println(T t) {
        System.out.println(t);
    }
}
